﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using PS.DBUtility;//Please add references
namespace PS.DAL
{
    /// <summary>
    /// 数据访问类:EwCheckPeriod
    /// 用于在每次任务单验收完成之后,将检修周期信息进行保存和更新
    /// 针对同类设备同类检修类别
    /// </summary>
    public partial class EwCheckPeriod
    {
        public EwCheckPeriod()
        { }

        #region  任务单验收完成需要调用的方法

        /// <summary>
        /// 任务单验收完成需要调用的方法
        /// </summary>
        /// <param name="EquipID">任务单号</param>
        public void TaskCheckPeriod(string TaskID)
        {
            try
            {
                string EquipID = DbHelperSQL.GetSingle("select EquipID from RepTask where TaskID = '" + TaskID + "'").ToString();
                if (EquipID != "" || EquipID != null)
                {
                    //查询是否包含'大修'
                    string isYes = DbHelperSQL.GetSingle(" select count(*) isYes from EwCheckRecords a,RepTask b,EwCheckType c  where a.TaskID = b.TaskID and b.TaskID='" +
                        TaskID + "' and a.EwCheckType = c.EwCheckType and c.CheckSubType='大修' and CheckType='大修'").ToString();
                    if (isYes == "0")//不包含
                    {

                        //运行日 计算模式为 1

                        //更新dbo.EwAlarmValue,计算平均运行天数
                        StringBuilder sb1 = new StringBuilder();
                        sb1.Append(" update a set a.AlarmValue = b.newAlarmValue  ");
                        sb1.Append(" from EwAlarmValue a,");
                        sb1.Append(" (select b.EwCheckType,b.EquipID,ceiling(sum(RunDays) / (b.checkcount - 1)) as newAlarmValue,checkcount");
                        sb1.Append(" from EwRundays a,");
                        sb1.Append(" (select min(a.CheckDate) minCheckDate,max(a.CheckDate) maxCheckDate");
                        sb1.Append(" ,count(*) checkcount,a.EwCheckType,b.EquipID");
                        sb1.Append(" from EwCheckRecords a,RepTask b,EwCheckType c");
                        sb1.Append(" where a.TaskID = b.TaskID");
                        sb1.Append(" and a.EwCheckType = c.EwCheckType");
                        sb1.Append(" and EquipID = '" + EquipID + "'");
                        sb1.Append(" and c.CalculateMode = 1");//--运行日计算模式
                        sb1.Append(" and b.TaskStatus=2");//--已完成的任务
                        sb1.Append(" group by b.EquipID,a.EwCheckType ");
                        sb1.Append(" having count(*) > 2");//---检修记录大于2,表示有两个周期
                        sb1.Append(" ) b");
                        sb1.Append(" where a.EquipID = b.EquipID ");
                        sb1.Append(" and a.EndDate < b.maxcheckdate"); //--结束时间小于最大的检修日期(累计第一次检修后的运行天数)
                        sb1.Append(" group by b.EwCheckType,b.EquipID,b.checkcount,b.mincheckdate");
                        sb1.Append(" having min(a.EndDate)>b.mincheckdate");// --最小的结束时间大于最小的检修日期(累计第一次检修后的运行天数)
                        sb1.Append(" ) b");
                        sb1.Append(" where a.EquipID = b.EquipID and a.EwCheckType = b.EwCheckType");
                        int rs = DbHelperSQL.ExecuteSql(sb1.ToString());

                        //更新上一个周期的周期信息
                        StringBuilder sb2 = new StringBuilder();
                        //if (rs > 0)//如果在EwAlarmValue更新过记录,则直接更新周期表中信息(两次检修记录以上)
                        //{
                        sb2.Append(" update EwCheckPeriod");
                        sb2.Append(" set EndDate=checkAll.lastEndDate");// --更新结束日期
                        sb2.Append(" ,RunDays=rundaySum.CurrentValue");//--更新运行天数
                        sb2.Append(" ,AvgRunDays=rundaySum.AlarmValue");//--更新平均运行天数
                        sb2.Append(" from dbo.EwCheckPeriod p,");
                        sb2.Append(" ( select b.EquipID,b.StartDate as lastEndDate ,a.EwCheckType, b.TaskID,c.CalculateMode from EwCheckRecords a,RepTask b,EwCheckType c  ");
                        sb2.Append(" where a.TaskID = b.TaskID and a.EwCheckType = c.EwCheckType and b.EquipID='" + EquipID + "' and c.CalculateMode=1 ) checkAll,");
                        sb2.Append(" ( select * from dbo.EwAlarmValue eav ) rundaySum ");
                        sb2.Append(" where p.EquipID=checkAll.EquipID and p.CheckType=checkAll.EwCheckType ");
                        sb2.Append(" and checkAll.TaskID='" + TaskID + "'");
                        sb2.Append(" and p.CalcMode=checkAll.CalculateMode and rundaySum.EquipID=checkAll.EquipID and ");
                        sb2.Append("  rundaySum.EwCheckType=checkAll.EwCheckType and p.EndDate is null");
                        int rs2 = DbHelperSQL.ExecuteSql(sb2.ToString());
                        //}
                        //else//则计算好平均运行日之后,再更新周期表中的信息(两次检修记录以内的)
                        //{

                        StringBuilder sb22 = new StringBuilder();
                        sb22.Append(" update EwCheckPeriod");
                        sb22.Append(" set EndDate=checkAll.lastEndDate");// --更新结束日期
                        sb22.Append(" ,RunDays=rundaySum.CurrentValue");//--更新运行天数
                        sb22.Append(" ,AvgRunDays=checkAll.newAlarmValue");//--更新平均运行天数
                        sb22.Append(" from dbo.EwCheckPeriod p, ");
                        //--查询出能找到最后一条检修周期记录的条件
                        sb22.Append(" (select b.EwCheckType,b.EquipID,ceiling(sum(RunDays) / (b.checkcount - 1)) as newAlarmValue,checkcount,b.StartDate as lastEndDate,b.CalculateMode ");
                        sb22.Append(" from EwRundays a, ");//--计算新的报警值
                        sb22.Append(" ( ");
                        sb22.Append(" select min(a.CheckDate) minCheckDate,max(a.CheckDate) maxCheckDate  ");
                        sb22.Append("  ,count(*) checkcount,a.EwCheckType,b.EquipID,max(b.StartDate) StartDate ,c.CalculateMode ");
                        sb22.Append(" from EwCheckRecords a,RepTask b,EwCheckType c  ");
                        sb22.Append(" where a.TaskID = b.TaskID  ");
                        sb22.Append(" and a.EwCheckType = c.EwCheckType  ");
                        sb22.Append(" and EquipID = '" + EquipID + "'  ");
                        sb22.Append(" and c.CalculateMode = 1 "); //--运行日计算模式
                        sb22.Append(" and b.TaskStatus=2 "); //--已完成的任务
                        sb22.Append(" group by b.EquipID,a.EwCheckType ,b.StartDate ,c.CalculateMode ");
                        sb22.Append(" having count(*) <= 2  ");//--检修记录在两次以内
                        sb22.Append(" ) b  ");
                        sb22.Append(" where a.EquipID = b.EquipID   ");
                        sb22.Append("  and a.EndDate < b.maxcheckdate "); //--结束时间小于最大的检修日期(累计第一次检修后的运行天数)
                        sb22.Append(" group by b.EwCheckType,b.EquipID,b.checkcount,b.mincheckdate ,b.StartDate,b.CalculateMode ");
                        sb22.Append(" having (b.checkcount - 1)<>0  and  min(a.EndDate)>b.mincheckdate  ");//--最小的结束时间大于最小的检修日期(累计第一次检修后的运行天数)
                        sb22.Append(" ) checkAll,  ");
                        sb22.Append(" (  "); //--查询已累计好的运行天数
                        sb22.Append(" select * from dbo.EwAlarmValue eav  ");
                        sb22.Append(" ) rundaySum ");
                        sb22.Append(" where p.EquipID=checkAll.EquipID  ");
                        sb22.Append(" and p.CheckType=checkAll.EwCheckType  ");
                        sb22.Append(" and p.CalcMode=checkAll.CalculateMode  ");
                        sb22.Append(" and rundaySum.EquipID=checkAll.EquipID  ");
                        sb22.Append(" and rundaySum.EwCheckType=checkAll.EwCheckType  ");
                        sb22.Append(" and p.EndDate is null ");//--为空表示上一条
                        //}
                        int rs3 = DbHelperSQL.ExecuteSql(sb22.ToString());

                        //更新dbo.EwAlarmValue,将CurrentValue清零
                        StringBuilder sb3 = new StringBuilder();
                        sb3.Append(" update a set CurrentValue = 0");
                        sb3.Append(" from EwAlarmValue a,");
                        sb3.Append(" (select b.EquipID,a.CheckDate,a.EwCheckType, c.CalculateMode");
                        sb3.Append(" from EwCheckRecords a,RepTask b,EwCheckType c ");
                        sb3.Append(" where a.TaskID = b.TaskID");
                        sb3.Append(" and b.TaskID='" + TaskID + "'");
                        sb3.Append(" and a.EwCheckType = c.EwCheckType");
                        sb3.Append(" and b.EquipID='" + EquipID + "'");
                        sb3.Append(" and c.CalculateMode=1");
                        sb3.Append(" ) checkAll");
                        sb3.Append(" where a.EquipID = checkAll.EquipID and a.EwCheckType = checkAll.EwCheckType");
                        int rs4 = DbHelperSQL.ExecuteSql(sb3.ToString());

                        //自然日  计算模式为 2


                        //上一个周期的周期信息
                        StringBuilder sbzzr1 = new StringBuilder();
                        sbzzr1.Append(" update p");
                        sbzzr1.Append(" set EndDate=checkAll.lastEndDate ");//--更新结束日期
                        sbzzr1.Append("  ,RunDays=datediff(day,(select max(CheckDate) from dbo.EwCheckRecords r");
                        sbzzr1.Append(" left join dbo.RepTask t on r.TaskID=t.TaskID");
                        sbzzr1.Append(" where EwCheckType=p.CheckType and t.EquipID=p.EquipID and r.TaskID <> '" + TaskID + "'),checkAll.lastEndDate)");//--更新运行天数
                        sbzzr1.Append(" from dbo.EwCheckPeriod p,");
                        sbzzr1.Append(" ( select b.EquipID,b.StartDate as lastEndDate ,a.EwCheckType, b.TaskID,c.CalculateMode from EwCheckRecords a,RepTask b,EwCheckType c  ");
                        sbzzr1.Append(" where a.TaskID = b.TaskID and a.EwCheckType = c.EwCheckType and b.EquipID='" + EquipID + "' and c.CalculateMode=2 ) checkAll,");
                        sbzzr1.Append(" ( select * from dbo.EwAlarmValue eav ) rundaySum ");
                        sbzzr1.Append(" where p.EquipID=checkAll.EquipID and p.CheckType=checkAll.EwCheckType ");
                        sbzzr1.Append(" and checkAll.TaskID='" + TaskID + "'");
                        sbzzr1.Append(" and p.CalcMode=checkAll.CalculateMode and rundaySum.EquipID=checkAll.EquipID and ");
                        sbzzr1.Append("  rundaySum.EwCheckType=checkAll.EwCheckType and p.EndDate is null");
                        int rs18 = DbHelperSQL.ExecuteSql(sbzzr1.ToString());

                        //更新上一个周期的平均自然日天数
                        StringBuilder sbzzr2 = new StringBuilder();
                        sbzzr2.Append(" update p ");
                        sbzzr2.Append(" set AvgRunDays=a.AvgRunDays");
                        sbzzr2.Append(" from  ");
                        sbzzr2.Append(" EwCheckPeriod p,( select ceiling((select  sum(RunDays)");
                        sbzzr2.Append(" from EwCheckPeriod pd where pd.EquipID=p.EquipID and pd.CheckType=p.CheckType and pd.CalcMode=p.CalcMode and pd.EndDate is not null)");
                        sbzzr2.Append(" /(select count(EwCheckPeriodID) ");
                        sbzzr2.Append(" from EwCheckPeriod pd where pd.EquipID=p.EquipID and pd.CheckType=p.CheckType and pd.CalcMode=p.CalcMode ");
                        sbzzr2.Append(" and pd.EndDate is not null))");
                        sbzzr2.Append(" AvgRunDays,p.EquipID,p.CheckType,p.EndDate,p.CalcMode ");
                        sbzzr2.Append("  from dbo.EwCheckPeriod p , ");
                        sbzzr2.Append(" ( select b.EquipID,b.StartDate as lastEndDate,a.EwCheckType, c.CalculateMode ");
                        sbzzr2.Append("  from EwCheckRecords a,RepTask b,EwCheckType c  where a.TaskID = b.TaskID ");
                        sbzzr2.Append(" and b.TaskID='" + TaskID + "' and a.EwCheckType = c.EwCheckType and b.EquipID='" + EquipID + "'");
                        sbzzr2.Append("  and c.CalculateMode=2 ) checkAll where p.EquipID=checkAll.EquipID   ");
                        sbzzr2.Append(" and p.CheckType=checkAll.EwCheckType and p.CalcMode=checkAll.CalculateMode and p.EndDate=checkAll.lastEndDate  ");
                        sbzzr2.Append(" group by p.RunDays,p.EquipID,p.CheckType,p.CalcMode, p.EndDate ) a ");
                        sbzzr2.Append(" where p.EquipID=a.EquipID and p.CheckType=a.CheckType and p.EndDate=a.EndDate and p.CalcMode =a.CalcMode");
                        int rs6 = DbHelperSQL.ExecuteSql(sbzzr2.ToString());

                        //更新dbo.EwAlarmValue 中AlarmValue
                        StringBuilder sbzrr3 = new StringBuilder();
                        sbzrr3.Append(" update a set a.AlarmValue = b.AvgRunDays");
                        sbzrr3.Append(" from EwAlarmValue a,");
                        sbzrr3.Append(" (select a.EwCheckType,b.EquipID	,p.AvgRunDays,b.TaskID");
                        sbzrr3.Append(" from EwCheckRecords a,RepTask b,EwCheckType c,dbo.EwCheckPeriod p");
                        sbzrr3.Append(" where a.TaskID = b.TaskID");
                        sbzrr3.Append(" and a.EwCheckType = c.EwCheckType");
                        sbzrr3.Append(" and b.EquipID = '" + EquipID + "'");
                        sbzrr3.Append(" and c.CalculateMode = 2");//--自然日计算模式
                        sbzrr3.Append(" and p.EquipID=b.EquipID");
                        sbzrr3.Append(" and p.CheckType=a.EwCheckType");
                        sbzrr3.Append(" group by b.EquipID,a.EwCheckType,p.AvgRunDays,b.TaskID");
                        sbzrr3.Append(" having count(*) > 2");//---检修记录大于2,表示有两个周期
                        sbzrr3.Append(" ) b");
                        sbzrr3.Append(" where a.EquipID = b.EquipID and a.EwCheckType = b.EwCheckType");
                        sbzrr3.Append(" and b.TaskID='" + TaskID + "'");
                        int rs7 = DbHelperSQL.ExecuteSql(sbzrr3.ToString());

                        //更新dbo.EwAlarmValue ,CurrentValue清零
                        StringBuilder sbzrr4 = new StringBuilder();
                        sbzrr4.Append(" update a set CurrentValue =0");
                        sbzrr4.Append(" from EwAlarmValue a,");
                        sbzrr4.Append(" (select a.EwCheckType,b.EquipID");
                        sbzrr4.Append(" from EwCheckRecords a,RepTask b,EwCheckType c");
                        sbzrr4.Append(" where a.TaskID = b.TaskID");
                        sbzrr4.Append(" and b.TaskID='" + TaskID + "'");
                        sbzrr4.Append(" and a.EwCheckType = c.EwCheckType");
                        sbzrr4.Append(" and b.EquipID = '" + EquipID + "'");
                        sbzrr4.Append(" and c.CalculateMode = 2");//--自然日计算模式
                        sbzrr4.Append(" ) b");
                        sbzrr4.Append(" where a.EquipID = b.EquipID and a.EwCheckType = b.EwCheckType");
                        int rs8 = DbHelperSQL.ExecuteSql(sbzrr4.ToString());


                        //新增一条或多条周期信息(运行日和自然日都要操作)
                        StringBuilder sbSelect = new StringBuilder();
                        sbSelect.Append(" select b.EquipID,a.CheckDate as StartDate,null as EndDate,a.EwCheckType as CheckType,");
                        sbSelect.Append(" c.CalculateMode as CalcMode,0 as RunDays,0 as AvgRunDays, 0 as Spare1");
                        sbSelect.Append(" , 0 as Spare2, 0 as Spare3, 0 as Spare4,'' as SpareString1,'' as SpareString2");
                        sbSelect.Append(" from EwCheckRecords a,RepTask b,EwCheckType c ");
                        sbSelect.Append("  where a.TaskID = b.TaskID");
                        sbSelect.Append(" and b.TaskID='" + TaskID + "'");
                        sbSelect.Append(" and a.EwCheckType = c.EwCheckType");
                        sbSelect.Append(" and b.EquipID='" + EquipID + "'");

                        DataTable dtSelect = DbHelperSQL.Query(sbSelect.ToString()).Tables[0];
                        StringBuilder sbInsert = new StringBuilder();
                        if (dtSelect != null && dtSelect.Rows.Count > 0)
                        {
                            foreach (DataRow drInser in dtSelect.Rows)
                            {
                                sbInsert.Append("insert into dbo.EwCheckPeriod values(");
                                sbInsert.Append("'" + drInser["EquipID"] + "',");
                                sbInsert.Append("'" + drInser["StartDate"] + "',");
                                sbInsert.Append("null,");
                                sbInsert.Append("'" + drInser["CheckType"] + "',");
                                sbInsert.Append("'" + drInser["CalcMode"] + "',");
                                sbInsert.Append("'" + drInser["RunDays"] + "',");
                                sbInsert.Append("'" + drInser["AvgRunDays"] + "',");
                                sbInsert.Append("'" + drInser["Spare1"] + "',");
                                sbInsert.Append("'" + drInser["Spare2"] + "',");
                                sbInsert.Append("'" + drInser["Spare3"] + "',");
                                sbInsert.Append("'" + drInser["Spare4"] + "',");
                                sbInsert.Append("'" + drInser["SpareString1"] + "',");
                                sbInsert.Append("'" + drInser["SpareString2"] + "'");
                                sbInsert.Append(");select @@IDENTITY; ");
                            }
                            int rs9 = DbHelperSQL.ExecuteSql(sbInsert.ToString());
                        }
                    }
                    else
                    {
                        //运行日 计算模式为 1

                        //更新dbo.EwAlarmValue,计算平均运行天数
                        StringBuilder sb1 = new StringBuilder();
                        sb1.Append(" update a set a.AlarmValue = b.newAlarmValue  ");
                        sb1.Append(" from EwAlarmValue a,");
                        sb1.Append(" (select b.EwCheckType,b.EquipID,ceiling(sum(RunDays) / (b.checkcount - 1)) as newAlarmValue,checkcount");
                        sb1.Append(" from EwRundays a,");
                        sb1.Append(" (select min(a.CheckDate) minCheckDate,max(a.CheckDate) maxCheckDate");
                        sb1.Append(" ,count(*) checkcount,a.EwCheckType,b.EquipID");
                        sb1.Append(" from EwCheckRecords a,RepTask b,EwCheckType c");
                        sb1.Append(" where a.TaskID = b.TaskID");
                        sb1.Append(" and a.EwCheckType = c.EwCheckType");
                        sb1.Append(" and EquipID = '" + EquipID + "'");
                        sb1.Append(" and c.CalculateMode = 1");//--运行日计算模式
                        sb1.Append(" and b.TaskStatus=2");//--已完成的任务
                        sb1.Append(" and c.CheckSubType='大修' and CheckType='大修'");
                        sb1.Append(" group by b.EquipID,a.EwCheckType");
                        sb1.Append(" having count(*) > 2");//---检修记录大于2,表示有两个周期
                        sb1.Append(" ) b");
                        sb1.Append(" where a.EquipID = b.EquipID ");
                        sb1.Append(" and a.EndDate < b.maxcheckdate"); //--结束时间小于最大的检修日期(累计第一次检修后的运行天数)
                        sb1.Append(" group by b.EwCheckType,b.EquipID,b.checkcount,b.mincheckdate");
                        sb1.Append(" having min(a.EndDate)>b.mincheckdate");// --最小的结束时间大于最小的检修日期(累计第一次检修后的运行天数)
                        sb1.Append(" ) b");
                        sb1.Append(" where a.EquipID = b.EquipID and a.EwCheckType = b.EwCheckType");
                        int rs = DbHelperSQL.ExecuteSql(sb1.ToString());

                        //更新上一个周期的周期信息
                        StringBuilder sb2 = new StringBuilder();
                        //if (rs > 0)//如果在EwAlarmValue更新过记录,则直接更新周期表中信息(两次检修记录以上)
                        //{ sb2.Append(" and c.CheckSubType='大修' and CheckType='大修'");
                        sb2.Append(" update p");
                        sb2.Append(" set EndDate=checkAll.lastEndDate");// --更新结束日期
                        sb2.Append(" ,RunDays=rundaySum.CurrentValue");//--更新运行天数
                        sb2.Append(" ,AvgRunDays=rundaySum.AlarmValue");//--更新平均运行天数
                        sb2.Append(" from dbo.EwCheckPeriod p,");
                        sb2.Append(" ( select b.EquipID,b.StartDate as lastEndDate ,a.EwCheckType, b.TaskID,c.CalculateMode from EwCheckRecords a,RepTask b,EwCheckType c  ");
                        sb2.Append(" where a.TaskID = b.TaskID and a.EwCheckType = c.EwCheckType and b.EquipID='" + EquipID + "' ");
                        sb2.Append(" and c.CheckSubType='大修' and CheckType='大修'");
                        sb2.Append(" and c.CalculateMode=1 ) checkAll,");
                        sb2.Append(" ( select * from dbo.EwAlarmValue eav ) rundaySum ");
                        sb2.Append(" where p.EquipID=checkAll.EquipID and p.CheckType=checkAll.EwCheckType ");
                        sb2.Append(" and checkAll.TaskID='" + TaskID + "'");
                        sb2.Append(" and p.CalcMode=checkAll.CalculateMode and rundaySum.EquipID=checkAll.EquipID and ");
                        sb2.Append("  rundaySum.EwCheckType=checkAll.EwCheckType and p.EndDate is null");
                        int rs10 = DbHelperSQL.ExecuteSql(sb2.ToString());
                        //}
                        //else//则计算好平均运行日之后,再更新周期表中的信息(两次检修记录以内的)
                        //{

                        StringBuilder sb22 = new StringBuilder();
                        sb22.Append(" update p");
                        sb22.Append(" set EndDate=checkAll.lastEndDate");// --更新结束日期
                        sb22.Append(" ,RunDays=rundaySum.CurrentValue");//--更新运行天数
                        sb22.Append(" ,AvgRunDays=checkAll.newAlarmValue");//--更新平均运行天数
                        sb22.Append(" from dbo.EwCheckPeriod p, ");
                        //--查询出能找到最后一条检修周期记录的条件
                        sb22.Append(" (select b.EwCheckType,b.EquipID,ceiling(sum(RunDays) / (b.checkcount - 1)) as newAlarmValue,checkcount,b.StartDate as lastEndDate,b.CalculateMode ");
                        sb22.Append(" from EwRundays a, ");//--计算新的报警值
                        sb22.Append(" ( ");
                        sb22.Append(" select min(a.CheckDate) minCheckDate,max(a.CheckDate) maxCheckDate  ");
                        sb22.Append("  ,count(*) checkcount,a.EwCheckType,b.EquipID,max(b.StartDate) StartDate ,c.CalculateMode ");
                        sb22.Append(" from EwCheckRecords a,RepTask b,EwCheckType c  ");
                        sb22.Append(" where a.TaskID = b.TaskID  ");
                        sb22.Append(" and a.EwCheckType = c.EwCheckType  ");
                        sb22.Append(" and EquipID = '" + EquipID + "'  ");
                        sb22.Append(" and c.CalculateMode = 1 "); //--运行日计算模式
                        sb22.Append(" and b.TaskStatus=2 "); //--已完成的任务
                        sb22.Append(" and c.CheckSubType='大修' and CheckType='大修'");
                        sb22.Append(" group by b.EquipID,a.EwCheckType ,b.StartDate ,c.CalculateMode ");
                        sb22.Append(" having count(*) <= 2  ");//--检修记录在两次以内
                        sb22.Append(" ) b  ");
                        sb22.Append(" where a.EquipID = b.EquipID   ");
                        sb22.Append("  and a.EndDate < b.maxcheckdate "); //--结束时间小于最大的检修日期(累计第一次检修后的运行天数)
                        sb22.Append(" group by b.EwCheckType,b.EquipID,b.checkcount,b.mincheckdate ,b.StartDate,b.CalculateMode ");
                        sb22.Append(" having (b.checkcount - 1)<>0  and  min(a.EndDate)>b.mincheckdate  ");//--最小的结束时间大于最小的检修日期(累计第一次检修后的运行天数)
                        sb22.Append(" ) checkAll,  ");
                        sb22.Append(" (  "); //--查询已累计好的运行天数
                        sb22.Append(" select * from dbo.EwAlarmValue eav  ");
                        sb22.Append(" ) rundaySum ");
                        sb22.Append(" where p.EquipID=checkAll.EquipID  ");
                        sb22.Append(" and p.CheckType=checkAll.EwCheckType  ");
                        sb22.Append(" and p.CalcMode=checkAll.CalculateMode  ");
                        sb22.Append(" and rundaySum.EquipID=checkAll.EquipID  ");
                        sb22.Append(" and rundaySum.EwCheckType=checkAll.EwCheckType  ");
                        sb22.Append(" and p.EndDate is null ");//--为空表示上一条
                        //}
                        int rs19 = DbHelperSQL.ExecuteSql(sb22.ToString());

                        //更新dbo.EwAlarmValue,将CurrentValue清零
                        StringBuilder sb3 = new StringBuilder();
                        sb3.Append(" update a set CurrentValue = 0");
                        sb3.Append(" from EwAlarmValue a,");
                        sb3.Append(" (select b.EquipID,a.CheckDate,a.EwCheckType, c.CalculateMode");
                        sb3.Append(" from EwCheckRecords a,RepTask b,EwCheckType c ");
                        sb3.Append(" where a.TaskID = b.TaskID");
                        sb3.Append(" and b.TaskID='" + TaskID + "'");
                        sb3.Append(" and a.EwCheckType = c.EwCheckType");
                        sb3.Append(" and b.EquipID='" + EquipID + "'");
                        sb3.Append(" ) checkAll");
                        sb3.Append(" where a.EquipID = checkAll.EquipID and a.EwCheckType = checkAll.EwCheckType");
                        int rs11 = DbHelperSQL.ExecuteSql(sb3.ToString());

                        //新增一条或多条周期信息(运行日和自然日都要操作)
                        StringBuilder sbSelect = new StringBuilder();
                        sbSelect.Append(" select b.EquipID,a.CheckDate as StartDate,null as EndDate,a.EwCheckType as CheckType,");
                        sbSelect.Append(" c.CalculateMode as CalcMode,0 as RunDays,0 as AvgRunDays, 0 as Spare1");
                        sbSelect.Append(" , 0 as Spare2, 0 as Spare3, 0 as Spare4,'' as SpareString1,'' as SpareString2");
                        sbSelect.Append(" from EwCheckRecords a,RepTask b,EwCheckType c ");
                        sbSelect.Append("  where a.TaskID = b.TaskID");
                        sbSelect.Append(" and b.TaskID='" + TaskID + "'");
                        sbSelect.Append(" and a.EwCheckType = c.EwCheckType");
                        sbSelect.Append(" and b.EquipID='" + EquipID + "'");
                        sbSelect.Append(" and c.CheckSubType='大修' and CheckType='大修'");

                        DataTable dtSelect = DbHelperSQL.Query(sbSelect.ToString()).Tables[0];
                        StringBuilder sbInsert = new StringBuilder();
                        if (dtSelect != null && dtSelect.Rows.Count > 0)
                        {
                            foreach (DataRow drInser in dtSelect.Rows)
                            {
                                sbInsert.Append("insert into dbo.EwCheckPeriod values(");
                                sbInsert.Append("'" + drInser["EquipID"] + "',");
                                sbInsert.Append("'" + drInser["StartDate"] + "',");
                                sbInsert.Append("null,");
                                sbInsert.Append("'" + drInser["CheckType"] + "',");
                                sbInsert.Append("'" + drInser["CalcMode"] + "',");
                                sbInsert.Append("'" + drInser["RunDays"] + "',");
                                sbInsert.Append("'" + drInser["AvgRunDays"] + "',");
                                sbInsert.Append("'" + drInser["Spare1"] + "',");
                                sbInsert.Append("'" + drInser["Spare2"] + "',");
                                sbInsert.Append("'" + drInser["Spare3"] + "',");
                                sbInsert.Append("'" + drInser["Spare4"] + "',");
                                sbInsert.Append("'" + drInser["SpareString1"] + "',");
                                sbInsert.Append("'" + drInser["SpareString2"] + "'");
                                sbInsert.Append(");select @@IDENTITY; ");
                            }
                        }
                        int rs12 = DbHelperSQL.ExecuteSql(sbInsert.ToString());
                    }
                }
                else
                {
                    new Exception("在任务单中没有找到设备!");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        #endregion

        #region  导入运行天数后需要调用的方法

        /// <summary>
        /// 导入运行天数后需要调用的方法
        /// </summary>
        /// <param name="returnStr">导入成功的设备</param>
        public void InputRunDaysCheckPeriod(string returnStr)
        {
            try
            {
                //将新增的设备的检修参数信息添加到EwAlarmValue中
                StringBuilder sbInsertEwAlarmValue = new StringBuilder();
                sbInsertEwAlarmValue.Append(" insert into EwAlarmValue(EquipID, EwCheckType, AlarmValue, CurrentValue, IfCancel)");
                sbInsertEwAlarmValue.Append(" select a.EquipID,c.EwCheckType,c.DefaultAlarmValue,0,0");
                sbInsertEwAlarmValue.Append(" from EquipInfo a,EquipType b,EwCheckType c");
                sbInsertEwAlarmValue.Append(" where a.TypeID = b.TypeID and b.TypeCode like c.EquipType + '%'");
                sbInsertEwAlarmValue.Append(" and not exists(select * from EwAlarmValue d where d.EquipID = a.EquipID and d.EwCheckType = c.EwCheckType)");
                int rssbInsertEwAlarmValue = DbHelperSQL.ExecuteSql(sbInsertEwAlarmValue.ToString());

                //---判断某一检修类别的开始检修日期是否是本次导入的起止日期之间
                //--在,运行天数＝（导入的结束日期－设备某一检修类别的检修日期） 与导入的运行天数比较取较小的值
                // --则更新dbo.EwCheckPeriod 中上一检修周期的真实运行天数
                StringBuilder sbrd1 = new StringBuilder();
                sbrd1.Append(" update a set RunDays= RunDays+newCurrentValue");
                sbrd1.Append(" from dbo.EwCheckPeriod a, (");
                sbrd1.Append(" select EwCheckType,s.EquipID, ");
                sbrd1.Append(" newCurrentValue=case when datediff(day,s.maxCheckDate,r.EndDate)<r.RunDays");
                sbrd1.Append(" then r.RunDays-datediff(day,s.maxCheckDate,r.EndDate)  else 0 end	");
                sbrd1.Append(" ,maxCheckDate from EwRunDays r,(select a.dd,a.cc,b.* from ");
                sbrd1.Append(" ( select MAX(a.BeginDate) dd, max(a.EndDate) cc,EquipID");
                sbrd1.Append(" from dbo.EwRunDays a group by a.EquipID  )a,");
                sbrd1.Append(" (select max(b.StartDate) ");
                sbrd1.Append(" maxCheckDate ,a.EwCheckType,b.EquipID from EwCheckRecords ");
                sbrd1.Append(" a,RepTask b,EwCheckType c where a.TaskID = b.TaskID and a.EwCheckType = ");
                sbrd1.Append(" c.EwCheckType and c.CalculateMode = 1 and b.TaskStatus=2 group by b.EquipID,a.EwCheckType");
                sbrd1.Append(" ) b  where a.EquipID = b.EquipID");
                sbrd1.Append(" and b.maxCheckDate between a.dd and a.cc)s");
                sbrd1.Append(" where r.EquipID=s.EquipID and r.BeginDate=s.dd and r.EndDate=s.cc");
                sbrd1.Append(" ) b where a.EquipID = b.EquipID and a.CheckType");
                sbrd1.Append(" = b.EwCheckType and a.EndDate=b.maxCheckDate  and a.EquipID in " + returnStr);//--相同结束日期..
                int rs12 = DbHelperSQL.ExecuteSql(sbrd1.ToString());


                //重新最后一检修周期计算运行天数平均值
                StringBuilder sbrd2 = new StringBuilder();
                sbrd2.Append(" update p set p.AvgRunDays=a.newAvgRunDays");
                sbrd2.Append(" from EwCheckPeriod p,");
                sbrd2.Append(" ( select ceiling(sum(a.RunDays) /count(EwCheckPeriodID)) as newAvgRunDays,b.EquipID,b.EwCheckType,b.maxCheckDate");
                sbrd2.Append(" from dbo.EwCheckPeriod a,");
                sbrd2.Append(" (select max(b.StartDate) maxCheckDate ,a.EwCheckType,b.EquipID");
                sbrd2.Append(" from EwCheckRecords a,RepTask b,EwCheckType c where a.TaskID = b.TaskID and a.EwCheckType = c.EwCheckType ");
                sbrd2.Append(" and c.CalculateMode = 1 and b.TaskStatus=2 group by b.EquipID,a.EwCheckType )b ");
                sbrd2.Append(" where a.EquipID = b.EquipID and a.CheckType = b.EwCheckType  and a.EndDate<=b.maxCheckDate");
                sbrd2.Append("  group by b.EquipID,b.EwCheckType,b.maxCheckDate");
                sbrd2.Append("  ) a ");
                sbrd2.Append("  where a.EquipID = p.EquipID and p.CheckType = a.EwCheckType  and p.EndDate=a.maxCheckDate and p.EquipID in " + returnStr);
                int rs13 = DbHelperSQL.ExecuteSql(sbrd2.ToString());

                //更新EwAlarmValue中的平均运行日
                StringBuilder sbrd3 = new StringBuilder();
                sbrd3.Append(" update a set  AlarmValue=p.AvgRunDays");
                sbrd3.Append(" from EwAlarmValue a,dbo.EwCheckPeriod p,");
                sbrd3.Append(" (select b.EwCheckType,b.EquipID, checkcount,maxCheckDate");
                sbrd3.Append(" from dbo.EwRunDays a,");
                sbrd3.Append(" (select max(b.StartDate) maxCheckDate");//任务单中的开始时间
                sbrd3.Append(" ,count(*) checkcount,a.EwCheckType,b.EquipID");
                sbrd3.Append(" from EwCheckRecords a,RepTask b,EwCheckType c");
                sbrd3.Append(" where a.TaskID = b.TaskID");
                sbrd3.Append(" and a.EwCheckType = c.EwCheckType");
                sbrd3.Append(" and c.CalculateMode = 1");//--运行日计算模式
                sbrd3.Append(" and b.TaskStatus=2");//--已完成的任务
                sbrd3.Append(" group by b.EquipID,a.EwCheckType");
                sbrd3.Append(" having count(*) > 2");//---检修记录大于2,表示有两个周期
                sbrd3.Append(" ) b");
                sbrd3.Append(" where a.EquipID = b.EquipID ");
                sbrd3.Append(" group by b.EwCheckType,b.EquipID,b.checkcount,b.maxCheckDate,a.EndDate,a.RunDays");
                sbrd3.Append(" having b.maxCheckDate between MAX(a.BeginDate) and max(a.EndDate)"); //--最大的检修记录是否在这一次导入运行天数的开始时间和结束时间
                sbrd3.Append(" ) b");
                sbrd3.Append(" where a.EquipID = b.EquipID and a.EwCheckType = b.EwCheckType");
                sbrd3.Append(" and p.EndDate=b.maxCheckDate ");
                sbrd3.Append(" and p.CheckType = b.EwCheckType and a.EquipID in " + returnStr);//--相同结束日期..
                int rs14 = DbHelperSQL.ExecuteSql(sbrd3.ToString());

                //更新EwAlarmValue 中,重新累计CurrentValue
                StringBuilder sbrd4 = new StringBuilder();
                sbrd4.Append(" update a set CurrentValue= CurrentValue+b.newCurrentValue");
                sbrd4.Append(" from EwAlarmValue a,");
                sbrd4.Append(" (select b.EwCheckType,b.EquipID, RunDays as newCurrentValue,checkcount");
                sbrd4.Append(" from EwRundays a,");
                sbrd4.Append(" (select max(a.CheckDate) maxCheckDate");
                sbrd4.Append(" ,count(*) checkcount,a.EwCheckType,b.EquipID");
                sbrd4.Append(" from EwCheckRecords a,RepTask b,EwCheckType c");
                sbrd4.Append(" where a.TaskID = b.TaskID");
                sbrd4.Append(" and a.EwCheckType = c.EwCheckType");
                sbrd4.Append(" and c.CalculateMode = 1");
                sbrd4.Append(" and b.TaskStatus=2");
                sbrd4.Append(" group by b.EquipID,a.EwCheckType");
                sbrd4.Append(" ) b");
                sbrd4.Append(" where a.EquipID = b.EquipID and a.EwRunDaysID in (select top 1 EwRunDaysID from EwRundays r where r.EquipID = b.EquipID order by EwRunDaysID desc)");
                sbrd4.Append(" group by b.EwCheckType,b.EquipID,b.checkcount,b.maxCheckDate,a.EndDate,a.RunDays");
                sbrd4.Append(" having b.maxCheckDate < MAX(a.BeginDate)"); //--最大的检修记录是否在这一次导入运行天数的开始时间之前
                sbrd4.Append(" ) b");
                sbrd4.Append(" where a.EquipID = b.EquipID and a.EwCheckType = b.EwCheckType and a.EquipID in " + returnStr);
                int rs15 = DbHelperSQL.ExecuteSql(sbrd4.ToString());

                StringBuilder sbrd5 = new StringBuilder();
                sbrd5.Append(" update a set  CurrentValue=b.newCurrentValue");
                sbrd5.Append(" from EwAlarmValue a,");
                sbrd5.Append(" (select b.EwCheckType,b.EquipID, ");
                sbrd5.Append(" newCurrentValue=case when datediff(day,b.maxCheckDate,a.EndDate)>a.RunDays");
                sbrd5.Append(" then a.RunDays");
                sbrd5.Append(" else datediff(day,b.maxCheckDate,a.EndDate)");
                sbrd5.Append(" end	,checkcount");
                sbrd5.Append(" from EwRundays a,");
                sbrd5.Append(" (select max(a.CheckDate) maxCheckDate");
                sbrd5.Append(" ,count(*) checkcount,a.EwCheckType,b.EquipID");
                sbrd5.Append(" from EwCheckRecords a,RepTask b,EwCheckType c");
                sbrd5.Append(" where a.TaskID = b.TaskID");
                sbrd5.Append(" and a.EwCheckType = c.EwCheckType");
                sbrd5.Append(" and c.CalculateMode = 1");
                sbrd5.Append(" and b.TaskStatus=2");
                sbrd5.Append(" group by b.EquipID,a.EwCheckType");
                sbrd5.Append(" ) b");
                sbrd5.Append(" where a.EquipID = b.EquipID   and a.EwRunDaysID in ");
                sbrd5.Append(" (select top 1 EwRunDaysID from EwRundays r where r.EquipID = b.EquipID order by EwRunDaysID desc)");
                //sbrd5.Append(" and a.EquipID not in (select b.EquipID");
                //sbrd5.Append(" from EwRundays a, (select max(a.CheckDate) maxCheckDate ,count(*) checkcount,a.EwCheckType,b.EquipID ");
                //sbrd5.Append(" from EwCheckRecords a,RepTask b,EwCheckType c where a.TaskID = b.TaskID and a.EwCheckType = c.EwCheckType");
                //sbrd5.Append(" and c.CalculateMode = 1 and b.TaskStatus=2 group by b.EquipID,a.EwCheckType ) b where a.EquipID = b.EquipID ");
                //sbrd5.Append("  group by b.EwCheckType,b.EquipID,b.checkcount,b.maxCheckDate,a.EndDate,a.RunDays ");
                //sbrd5.Append(" having b.maxCheckDate < MAX(a.BeginDate) )");
                sbrd5.Append(" group by b.EwCheckType,b.EquipID,b.checkcount,b.maxCheckDate,a.EndDate,a.RunDays");
                sbrd5.Append(" having b.maxCheckDate between MAX(a.BeginDate) and max(a.EndDate) ");//最大的检修记录是否在这一次导入运行天数的开始时间和结束时间
                sbrd5.Append(" ) b");
                sbrd5.Append(" where a.EquipID = b.EquipID and a.EwCheckType = b.EwCheckType and a.EquipID in " + returnStr);
                int rs16 = DbHelperSQL.ExecuteSql(sbrd5.ToString());


                //给没有检修记录的设备导入天数时,也要更新
                StringBuilder sbSetCurrentValue = new StringBuilder();
                sbSetCurrentValue.Append(" update a set a.CurrentValue = b.runDays");
                sbSetCurrentValue.Append(" from");
                sbSetCurrentValue.Append(" EwAlarmValue a,");
                sbSetCurrentValue.Append(" (select a.EquipID,sum(RunDays) runDays from EwRunDays a");
                sbSetCurrentValue.Append(" group by a.EquipID) b,EwCheckType c");
                sbSetCurrentValue.Append(" where a.EquipID = b.EquipID and a.EwCheckType = c.EwCheckType");
                sbSetCurrentValue.Append(" and c.CalculateMode = 1");
                sbSetCurrentValue.Append(" and not exists(select * from EwCheckRecords d,RepTask e");
                sbSetCurrentValue.Append("  where d.TaskID = e.TaskID ");
                sbSetCurrentValue.Append(" and e.EquipID = a.EquipID");
                sbSetCurrentValue.Append(" and e.TaskStatus=2 ");
                sbSetCurrentValue.Append(" and d.EwCheckType = a.EwCheckType)");
                sbSetCurrentValue.Append(" and a.EquipID in " + returnStr);
                int rsSetCurrentValue = DbHelperSQL.ExecuteSql(sbSetCurrentValue.ToString());

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        #endregion
    }
}
